﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmTrialBalanceI

    Dim ColDate, TotalIn, TotalOut, Currency, CurrencyCond As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
        Me.ErrProv.Clear()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("", cnn)

            If Me.RForAPeriod.Checked = True Then
                dap.SelectCommand.CommandText = "Select AccNo,Acc1,Acc2,Acc3,Acc4,TotalIn,TotalOut " & _
                                                "From Transactions Where " & _
                                                "TransDate >= N'" & Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00' and " & _
                                                "TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & CurrencyCond
                dap.SelectCommand.Connection = cnn

            ElseIf Me.RClosingBalances.Checked = True Then
                dap.SelectCommand.CommandText = "Select AccNo,Acc1,Acc2,Acc3,Acc4,TotalIn,TotalOut " & _
                                                "From Transactions Where  " & _
                                                "TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & CurrencyCond
                dap.SelectCommand.Connection = cnn
            End If

            Dim das As New DataSet

            dap.Fill(das, "Transactions")

            If Me.RLevel1.Checked = True Then
                If Me.RForAPeriod.Checked = True Then
                    ShowLevel1ForAPeriod(das)

                ElseIf Me.RClosingBalances.Checked = True Then
                    ShowLevel1ClosingBalance(das)
                End If

            ElseIf Me.RLevel2.Checked = True Then
                If Me.RForAPeriod.Checked = True Then
                    ShowLevel2ForAPeriod(das)

                ElseIf Me.RClosingBalances.Checked = True Then
                    ShowLevel2ClosingBalance(das)
                End If

            ElseIf Me.RLevel3.Checked = True Then
                If Me.RForAPeriod.Checked = True Then
                    ShowLevel3ForAPeriod(das)

                ElseIf Me.RClosingBalances.Checked = True Then
                    ShowLevel3ClosingBalance(das)
                End If

            ElseIf Me.RLevel4.Checked = True Then
                If Me.RForAPeriod.Checked = True Then
                    ShowLevel4ForAPeriod(das)

                ElseIf Me.RClosingBalances.Checked = True Then
                    ShowLevel4ClosingBalance(das)
                End If
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel1ForAPeriod(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel1
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel1ClosingBalance(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel1ClosingBalance
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel2ForAPeriod(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel2
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel2ClosingBalance(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel2ClosingBalance
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel3ForAPeriod(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel3
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel3ClosingBalance(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel3ClosingBalance
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel4ForAPeriod(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel4
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowLevel4ClosingBalance(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New TrialBalanceLevel4ClosingBalance
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmTrialBalance_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub

    Sub CheckRadioDate()
        If Me.RForAPeriod.Checked = True Then
            Me.DTPPeriodFrom.Enabled = True

        ElseIf Me.RClosingBalances.Checked = True Then
            Me.DTPPeriodFrom.Enabled = False
        End If
    End Sub

    Private Sub RForAPeriod_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RForAPeriod.CheckedChanged
        CheckRadioDate()
    End Sub

    Private Sub RClosingBalances_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RClosingBalances.CheckedChanged
        CheckRadioDate()
    End Sub
End Class